Team 11 - Project Workflow and Report

Project Description

For this project we have three datasets. Austin covid cases over time, Austin animal center intakes and Austin animal center outcomes. The datasets include details about animal shelter intakes for dogs and cats and the outcomes. The idea of the project is to analyze the trends in Intake and Outcome in Animal Shelters and also analyze the impact of COVID on these trends.

The main goal of this project is to analyze the impact of COVID cases on animal shelter intakes and outcomes.

  1. The Intake types for Animal Shelters include ‘Stray’, ‘Owner Surrender’ etc. We would like to compare the intake trends pre-covid and during covid. Did the animal shelter have more ‘Stray’ and ‘Owner Surrender’ intakes during covid than before?
  2. The Outcomes types include ‘Adoption’, ‘Return to Owner’ among other types. We would like to compare the adoption trends pre-covid and during covid. Did the adoptions go up during covid?

ER Diagram

erdiagram.png

rel-vocab.PNG

Sample Table Structure

sample_table.PNG

Data Sources

Animal Shelter Intakes

Source: https://data.austintexas.gov/Health-and-Community-Services/Austin-Animal-Center-Intakes/wter-evkm/data

CSV: https://drive.google.com/file/d/1AmbvO3lrdY6QuA3WlpbV7GVeYEq6E_NO/view?usp=sharing

intake.png

Animal Shelter Outcomes

Source:https://data.austintexas.gov/Health-and-Community-Services/Austin-Animal-Center-Outcomes/9t4d-g238/data

CSV:https://drive.google.com/file/d/14utaIYAKap2ahKf4xh-6Y98yzkQtLAVV/view?usp=sharing

outcome.png

COVID cases in Travis County

Source:https://experience.arcgis.com/experience/3793562ab87e4299b106e0c282bb1fc5

CSV:https://drive.google.com/file/d/1QEesdn-wnp1_zEnfin5yxXV57fiVfyDW/view?usp=sharing

covid.png

Intake CSV Export

While exporting the Intake csv, we had to add a filter for 'Found Location' to make sure that our dataset did not contain data for locations outside Travis County as our covid dataset was for Travis County.

export.png

Setting PHPMYSQL connection

Truncating Tables

Inserting into Database

Writing into animals, shelter_events & dates table from Austin_Animal_Center_Intake.csv file

Writing into animals, shelter_events & dates table from Austin_Animal_Center_Outcomes.csv file

Data inserted into animals table

animals_data.PNG

Data inserted into shelter_events table

shelter_events_intake.PNG

shelter_events_outcome.PNG

Data inserted into dates table

dates.PNG

Writing into covid_case_counts and updating dates tables from Austin_Travis_County_COVID19_Daily_Counts.csv file

Data inserted in covid_case_counts table

covid_case_counts.PNG

dates table update with covid_id

dates_covid_updated.PNG

SQL queries for fetching data for analysis

We used the below queries to fetch the required data from phpmyadmim for analysis. We exported the CSVs to use in Tableau.

SQL-export-1.PNG

SQL-export-2.PNG

SQL-export-3.PNG

CSVs used for Analysis

csv1.PNG

csv2.PNG

csv3.PNG

Data Analysis using Tableau

We used Tableau to analyze the data. Our goal was to see how the year 2020 compared to the previous years in terms of Intakes/Outcomes for the Austin Animal Shelter and compare the number of intakes/outcomes to the number of reported COVID cases from March 2020 - December 2020.

We chose to focus on two intake types - 'Stray' and 'Owner Surrender'. We have two line charts that compare the total number of Stray/Owner surrender intakes for each month for the years 2017 - 2020. We observed that the number of Stray/Owner Surrender intakes were significantly lower in the year 2020. For the years 2017 - 2019, the number of intakes were gradually increasing during the months of Feb - May, but in the year 2020, the Stray intakes decreased during these months.

Note: The chart comparing Stray and Owner Surrender Intakes with COVID starts from 13th March, 2020 to take the covid dataset into account. The other individual charts for comparing trends for 'Stray' and 'Owner surrender' take the total number of events in each month into account.

Intake%20dashboard.png

We chose to focus on two outcome types - 'Adoption' & 'Transfer'. We have two line charts that compare the total number of Adoption/Transfer outcomes for each month for the years 2017 - 2020. We observed that although the overall adoption and transfer outcomes were lower in the year 2020, the number of adoptions gradually increased from July to November in 2020.

outcome%20dashboard.png

Challenges

One of the challenges we faced was with the date format. As the format was different across CSVs, we had to format the date in python before inserting into the database. As we had a lot of records to process, the insertion was taking a long time. We tried to address this by performing a COMMIT only after all the records were inserted instead of autocommit. We also had to take care of empty values while inserting data into the database. Another confusion was regarding the insertion of data into the dates table. We were initially doing this at the end, after inserting data into the animals and shelter_events tables. But, after receiving feedback of a better way to insert data, we modified our code to insert data into the dates table along with the animals and shelter_events table.

Tool for Analysis

We decided to use Tableau for analysis. We were familiar with this tool as we had worked with it previously in the Data Storytelling course.